﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Pub;
using System.Configuration;
using System.Threading;
using LZQXT.Test.Forms.DB;
using ICSharpCode.TextEditor;
using ICSharpCode.TextEditor.Document;

namespace LZQXT.Test.Forms
{
    public partial class FDBMgr : Form
    {


        public delegate void tv1AddNodeCallBack(TreeNode node1);
        public delegate void tv1ClsCallBack();

        ADOTest ado1 = new ADOTest();
        ADOTest ado2 = new ADOTest();
        string sDBNam = "";
        string sTable = "";
        string sField = "";

        DataSet ds1 = null;
        int i_RowIndex = 0;

        public TextEditorControl tboxSQL;
        public CTextEditMenu tboxSQL_Menu;


        private void FQuery_Load(object sender, EventArgs e)
        {
            dataGridView1.Dock = DockStyle.Fill;
            dataGridView2.Dock = DockStyle.Fill;
            treeView1.Dock = DockStyle.Fill;
            tabControl1.Dock = DockStyle.Fill;

            Program.fdbrow.FormBorderStyle = FormBorderStyle.None;
            Program.fdbrow.TopLevel = false;
            Program.fdbrow.Parent = tabPage3;
            Program.fdbrow.Dock = DockStyle.Fill;
            Program.fdbrow.Show();

            panel_SQL.Dock = DockStyle.Fill;
            tboxSQL = new TextEditorControl();
            tboxSQL.BorderStyle = BorderStyle.Fixed3D;
            tboxSQL.Parent = panel_SQL;
            tboxSQL.Dock = DockStyle.Fill;
            tboxSQL.Show();

            tboxSQL.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            tboxSQL.Encoding = System.Text.Encoding.Default;
            tboxSQL.ActiveTextAreaControl.TextArea.KeyDown += tbox_KeyDown;

            tabControl1.SelectedIndex = 0;

            tboxSQL_Menu = new CTextEditMenu(tboxSQL);
            //CGridViewMenu gridMenu = new CGridViewMenu(dataGridView1, ado1);
        }

        private void FQuery_Shown(object sender, EventArgs e)
        {
            Thread thread1 = new Thread(fillTree);
            thread1.Start();
            //fillTree();
        }


        public void tv1AddNode(TreeNode node1)
        {
            try
            {
                if (treeView1.InvokeRequired)
                {
                    tv1AddNodeCallBack tbc = new tv1AddNodeCallBack(tv1AddNode);
                    this.Invoke(tbc, new object[] { node1 });
                }
                else
                {
                    treeView1.Nodes.Add(node1);
                }
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
            }
        }

        public void tv1Cls()
        {
            try
            {
                if (treeView1.InvokeRequired)
                {
                    tv1ClsCallBack tbc = new tv1ClsCallBack(tv1Cls);
                    this.Invoke(tbc, new object[] { });
                }
                else
                {
                    treeView1.Nodes.Clear();
                }
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
            }
        }

        public FDBMgr()
        {
            InitializeComponent();
        }


        private void btn_Click(object sender, EventArgs e)
        {
            int i_btn = 0;
            if (sender is Button) i_btn = Convert.ToInt32(((Button)sender).Tag);
            if (sender is ToolStripButton) i_btn = Convert.ToInt32(((ToolStripButton)sender).Tag);
            if (sender is ToolStripMenuItem) i_btn = Convert.ToInt32(((ToolStripMenuItem)sender).Tag);
            if (sender is Int32) i_btn = Convert.ToInt32(sender);
            if (sender is MenuItem) i_btn = Convert.ToInt32(((MenuItem)sender).Tag);

            String strSQL = "";

            switch (i_btn)
            {
                case 1:

                    try
                    {
                        if (tboxSQL_Menu.SelectedText.Length > 0) strSQL = tboxSQL_Menu.SelectedText;
                        else strSQL = tboxSQL.Text;

                        strSQL = strSQL.Replace("\r", " ");
                        strSQL = strSQL.Replace("\n", " ");
                        ds1 = ado1.sel1(strSQL);
                        labCount.Text = "return: " + ado1.ds_count;

                        //数据库名称
                        string sDBNam_temp = CPub.findStrBetween(strSQL.Replace("\n", " ").ToLower(), "use", "").Trim();
                        string sDBNam_temp_2 = CPub.findStrBetween(sDBNam_temp, "", " ");
                        if (sDBNam_temp_2 != "") sDBNam = sDBNam_temp_2;
                        else if (sDBNam_temp != "") sDBNam = sDBNam_temp;
                        //表名
                        string sTabNam_temp = CPub.findStrBetween(strSQL.Replace("\n", " ").ToLower(), "from", "").Trim();
                        string sTabNam_temp_2 = CPub.findStrBetween(sTabNam_temp, "", " ");
                        if (sTabNam_temp_2 != "") sTable = sTabNam_temp_2;
                        else sTable = sTabNam_temp;

                        labDBName.Text = sDBNam;
                        labTabName.Text = sTable;

                        //显示数据
                        if (!CDB.dsIsNull(ds1)) dataGridView1.DataSource = ds1.Tables[0];
                        else dataGridView1.DataSource = null;
                    }
                    catch (System.Exception ex)
                    {
                        dataGridView1.DataSource = null;
                        CDebug.EnterMethod("发生异常");
                        CDebug.Send(strSQL);
                        CDebug.SendError(ex.ToString());
                        //tboxSQL_Menu.AppendText("-- " + ex.ToString());
                        MessageBox.Show(ex.ToString());
                        CDebug.ExitMethod("发生异常");
                    }
                    break;
                case 2:
                    try
                    {
                        if (tboxSQL_Menu.SelectedText.Length > 0) strSQL = tboxSQL_Menu.SelectedText;
                        else strSQL = tboxSQL.Text;


                        if (CPub.MsgBox("确定要更新数据库吗？\n" + strSQL, "更新"))
                        {
                            ado1.cmd1(strSQL);
                        }

                    }
                    catch (System.Exception ex)
                    {
                        dataGridView1.DataSource = null;
                        CDebug.EnterMethod("发生异常");
                        CDebug.Send(strSQL);
                        CDebug.SendError(ex.ToString());
                        tboxSQL_Menu.AppendText("-- " + ex.ToString());
                        CDebug.ExitMethod("发生异常");
                    }

                    break;

                case 11:
                    //Thread thread1 = new Thread(fillTree);
                    //thread1.Start();
                    refTree();
                    break;
                case 12:
                    setKey(this.sTable, this.sField);
                    break;
                case 13:
                    dropTable();
                    break;


                case 31:    //add ado
                    FDBLogin fdblogin = new FDBLogin();
                    fdblogin.ShowDialog();
                    ado1.S_Con = Program.fdb_tree.ado1.S_Con;
                    ado2.S_Con = Program.fdb_tree.ado1.S_Con;
                    btn_Click(11, null);
                    break;
                case 32:    //close ado
                    treeView1.Nodes.Clear();
                    break;
                case 33:    //refresh ado
                    btn_Click(11, null);
                    break;
            }
        }

        private void tbox_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F5)
            {
                btn_Click(btnSel, null);
            }
        }

        /// <summary>
        /// 生成数据库名称，表名，字段名
        /// </summary>
        private void fillTree()
        {
            if (ado1.S_Con == null || ado1.S_Con == "") return;
            //treeView1.Nodes.Clear();
            tv1Cls();

            //Dictionary<string, string>.KeyCollection dicKey = ADOTest.dicConStr.Keys;

            //foreach (string sDBConKey in dicKey)
            //{
            //ADOTest ado_Test = new ADOTest(sDBConKey);
            ADOTest ado_Test = new ADOTest();
            ado_Test.S_Con = ado1.S_Con;
            DataSet ds1 = ado_Test.sel1("SELECT Name FROM Master..SysDatabases ORDER BY Name");
            if (ds1 == null || ds1.Tables.Count == 0) return;
            TreeNode node0 = new TreeNode();
            node0.Text = CPub.findStrBetween(ado1.S_Con, "=", ";");
            node0.Tag = 0;
            node0.ImageIndex = 0;
            foreach (DataRow dr1 in ds1.Tables[0].Rows)
            {
                String DBName = dr1["Name"].ToString();
                TreeNode node1 = new TreeNode();
                node1.Text = DBName;
                node1.Tag = 1;
                node1.ImageIndex = 1;

                node0.Nodes.Add(node1);
            }
            //treeView1.Nodes.Add(node0);
            tv1AddNode(node0);
            //}
        }

        private void refTree()
        {
            if (treeView1.SelectedNode == null)
            {
                Thread thread1 = new Thread(fillTree);
                thread1.Start();
            }
            else
            {
                TreeNode node1 = treeView1.SelectedNode;
                if (Convert.ToInt32(node1.Tag) == 0)
                {
                    #region 服务器
                    Thread thread1 = new Thread(fillTree);
                    thread1.Start();
                    #endregion
                }
                if (Convert.ToInt32(node1.Tag) == 1)
                {
                    #region 数据库
                    node1.Nodes.Clear();
                    String DBName = node1.Text;
                    DataSet ds2 = ado1.sel1("SELECT Name FROM " + DBName + "..SysObjects Where XType='U' ORDER BY Name ");
                    if (ds2 != null && ds2.Tables.Count > 0)
                    {
                        foreach (DataRow dr2 in ds2.Tables[0].Rows)
                        {
                            String TabName = dr2["Name"].ToString();
                            TreeNode node2 = new TreeNode();
                            node2.Text = TabName;
                            node2.Tag = 2;
                            node2.ImageIndex = 2;

                            DataSet ds3 = ado1.sel1("SELECT Name FROM SysColumns WHERE id=Object_Id('" + TabName + "') ");
                            if (ds3 != null && ds3.Tables.Count > 0)
                            {
                                foreach (DataRow dr3 in ds3.Tables[0].Rows)
                                {
                                    String ColName = dr3["Name"].ToString();
                                    TreeNode node3 = new TreeNode();
                                    node3.Text = ColName;
                                    node3.Tag = 3;
                                    node3.ImageIndex = 3;
                                    node2.Nodes.Add(node3);
                                }
                            }
                            node1.Nodes.Add(node2);
                        }
                    }

                    String strSQL = "SP_HELPDB " + node1.Text + "\n";
                    DataSet ds1 = ado2.sel1(strSQL);
                    if (ds1 == null || ds1.Tables.Count == 0)
                    {
                        dataGridView1.DataSource = null;
                        return;
                    }
                    if (ds1.Tables.Count > 1)
                        dataGridView2.DataSource = ds1.Tables[1];
                    #endregion

                }
            }

        }

        /// <summary>
        /// 创建主键，没有主键的表无法更新数据
        /// </summary>
        private void setKey(string sTab, string sCol)
        {
            CDebug.EnterMethod("设置主键");
            try
            {
                string sKeyName = "PK_" + sTab;
                string strSQL = "select * from sysobjects where name = '" + sKeyName + "'";
                DataSet ds1 = ado1.sel1(strSQL);

                //如果主键名称存在，则重命名
                int i = 1;
                while (ado1.ds_count > 0)
                {
                    sKeyName = "PK_" + sTab + "_" + i;
                    strSQL = "select * from sysobjects where name = '" + sKeyName + "'";
                    ds1 = ado1.sel1(strSQL);
                    i++;
                }

                strSQL = "ALTER TABLE [" + sTab + "] ADD  CONSTRAINT [" + sKeyName + "] PRIMARY KEY CLUSTERED \n"
                   + "( [" + sCol + "] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, \n"
                   + "IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]";
                CDebug.Send(strSQL);
                ado1.cmd1(strSQL);
            }
            catch (System.Exception ex)
            {
                CDebug.SendError("设置主键出错: " + ex.ToString());
            }

            CDebug.ExitMethod("设置主键");
        }

        /// <summary>
        /// 删除主键
        /// </summary>
        /// <param name="sTab"></param>
        /// <param name="sKey"></param>
        private void delKey(string sTab, string sKey)
        {
            CDebug.EnterMethod("删除主键");
            try
            {
                string strSQL = "ALTER " + sTab
                    + " DROP CONSTRAINT " + sKey + "\n "
                    + " ALTER TABLE " + sKey + " SET (LOCK_ESCALATION = TABLE) \n"
                    + " COMMIT ";
                CDebug.Send(strSQL);
                ado1.cmd1(strSQL);
            }
            catch (System.Exception ex)
            {
                CDebug.SendError("删除主键出错: " + ex.ToString());
            }

            CDebug.ExitMethod("删除主键");

        }

        private void dropTable()
        {
            if (CPub.MsgBox("确定要删除表[" + sTable + "]吗？", "删除"))
            {
                String strSQL = "DROP TABLE [" + sTable + "]";
                ado1.cmd1(strSQL);
                treeView1.SelectedNode.Remove();
            }

        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            try
            {
                treeView1.SelectedNode.SelectedImageIndex = treeView1.SelectedNode.ImageIndex;
                btnKey.Enabled = false;
                btnDropTable.Enabled = false;
                btnRef.Enabled = false;

                if (treeView1.SelectedNode == null) return;
                TreeNode node1 = treeView1.SelectedNode;
                if (Convert.ToInt32(node1.Tag) == 0)
                {
                    #region 服务器
                    btnRef.Enabled = true;
                    #endregion
                }
                if (Convert.ToInt32(node1.Tag) == 1)
                {
                    #region 数据库
                    //node1.ImageIndex = 6;
                    //node1.SelectedImageIndex = node1.ImageIndex;
                    sDBNam = CPub.findStrBetween(ado1.S_Con, "Catalog=", ";");
                    if (sDBNam == "")
                        ado1.S_Con = ado1.S_Con.Replace("Catalog=;", "Catalog=" + node1.Text + ";");
                    else
                        ado1.S_Con = ado1.S_Con.Replace(sDBNam, node1.Text);

                    ado2.S_Con = ado1.S_Con;

                    btnRef.Enabled = true;
                    if (node1.Nodes.Count == 0)
                    {
                        String DBName = node1.Text;
                        DataSet ds2 = ado1.sel1("SELECT Name FROM " + DBName + "..SysObjects Where XType='U' ORDER BY Name ");
                        if (ds2 != null && ds2.Tables.Count > 0)
                        {
                            foreach (DataRow dr2 in ds2.Tables[0].Rows)
                            {
                                String TabName = dr2["Name"].ToString();
                                TreeNode node2 = new TreeNode();
                                node2.Text = TabName;
                                node2.Tag = 2;
                                node2.ImageIndex = 2;

                                DataSet ds3 = ado1.sel1("SELECT Name FROM SysColumns WHERE id=Object_Id('" + TabName + "') ");
                                if (ds3 != null && ds3.Tables.Count > 0)
                                {
                                    foreach (DataRow dr3 in ds3.Tables[0].Rows)
                                    {
                                        String ColName = dr3["Name"].ToString();
                                        TreeNode node3 = new TreeNode();
                                        node3.Text = ColName;
                                        node3.Tag = 3;
                                        node3.ImageIndex = 3;
                                        node2.Nodes.Add(node3);
                                    }
                                }
                                node1.Nodes.Add(node2);
                            }
                        }
                    }

                    String strSQL = "SP_HELPDB " + node1.Text + "\n";
                    DataSet ds1 = ado2.sel1(strSQL);
                    if (ds1 == null || ds1.Tables.Count == 0)
                    {
                        dataGridView1.DataSource = null;
                        return;
                    }
                    if (ds1.Tables.Count > 1)
                        dataGridView2.DataSource = ds1.Tables[1];
                    #endregion
                }
                if (Convert.ToInt32(node1.Tag) == 2)
                {
                    #region 表
                    //String strSQL = "USE " + node1.Parent.Text + "\n";
                    //ado2.cmd1(strSQL);
                    sTable = node1.Text;
                    //strSQL = "SP_HELP " + node1.Text + "\n";
                    string strSQL = "SELECT \n"
                        + "(case when a.colorder=1 then d.name else '' end) as 表名, \n"
                        + "     a.colorder as 字段序号, \n"
                        + "     a.name as 字段名, \n"
                        + "     (case when COLUMNPROPERTY( a.id,a.name, 'IsIdentity' )=1 then '√' else '' end) as 标识,  \n"
                        + "     (case when (SELECT count(*) FROM sysobjects \n"
                        + "                     WHERE (name in \n"
                        + "                             (SELECT name FROM sysindexes \n"
                        + "                             WHERE (id = a.id)   AND (indid in \n"
                        + "                                     (SELECT indid FROM sysindexkeys \n"
                        + "                                       WHERE (id = a.id) AND (colid in \n"
                        + "                                         (SELECT colid FROM syscolumns \n"
                        + "                                         WHERE (id = a.id) AND (name = a.name)) \n"
                        + "                         ))))) \n"
                        + "         AND (xtype = 'PK' ))>0 then '√' else '' end) as 主键, \n"
                        + "b.name as 类型, \n"
                        + "a.length as 占用字节数,  \n"
                        + "COLUMNPROPERTY(a.id,a.name,'PRECISION' ) as    长度,  \n"
                        + "isnull(COLUMNPROPERTY(a.id,a.name,'Scale' ),0) as 小数位数, \n"
                        + "(case when a.isnullable=1 then '√' else '' end) as 允许空, \n"
                        + "isnull(e.text,'' ) as 默认值, \n"
                        + "isnull(g.[value],'' ) AS 字段说明 \n"
                        + "FROM syscolumns a left join systypes b  \n"
                        + "on a.xtype=b.xusertype \n"
                        + "inner join sysobjects d \n"
                        + "on a.id=d.id and d.xtype='U' and d.name<> 'dtproperties'  \n"
                        + "left join syscomments e \n"
                        + "on a.cdefault=e.id \n"
                        + "left join sys.extended_properties g \n"
                        + "on a.id=g.major_id AND a.colid = g.minor_id  \n"
                        + "     where d.name='" + node1.Text + "' \n"
                        + "order by a.id,a.colorder \n";

                    //CDebug.Send(strSQL);
                    DataSet ds1 = ado2.sel1(strSQL);
                    if (ds1 == null || ds1.Tables.Count == 0)
                    {
                        dataGridView1.DataSource = null;
                        return;
                    }
                    if (ds1.Tables.Count > 0)
                    {
                        dataGridView2.DataSource = ds1.Tables[0];
                        if (node1.Nodes.Count == 0)
                        {
                            strSQL = "SP_HELP " + node1.Text + "\n";
                            ds1 = ado2.sel1(strSQL);
                            for (int i = 0; i < ds1.Tables[1].Rows.Count; i++)
                            {
                                DataRow dr1 = ds1.Tables[1].Rows[i];
                                String ColName = dr1["Column_name"].ToString();
                                TreeNode node3 = new TreeNode();
                                node3.Text = ColName;
                                node3.Tag = 3;
                                node3.ImageIndex = 3;
                                node1.Nodes.Add(node3);
                            }
                        }
                    }
                    btnDropTable.Enabled = true;
                    #endregion
                }
                if (Convert.ToInt32(node1.Tag) == 3)
                {
                    #region 字段
                    sTable = node1.Parent.Text;
                    sField = node1.Text;
                    btnKey.Enabled = true;
                    #endregion
                }
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
            }

        }

        private void treeView1_DoubleClick(object sender, EventArgs e)
        {
            if (treeView1.SelectedNode == null) return;

            try
            {
                TreeNode node1 = treeView1.SelectedNode;
                if (Convert.ToInt32(node1.Tag) == 2)
                {
                    /*
                    String strSQL = "USE " + node1.Parent.Text + "\n";
                    tboxSQL.AppendText(strSQL);
                    ado1.cmd1(strSQL);
                    */
                    string strSQL = "USE " + node1.Parent.Text + "\n"
                        + "SELECT TOP 1000 * FROM " + node1.Text + "\n";
                    tboxSQL_Menu.AppendText(strSQL);
                    ds1 = ado1.sel1(strSQL);
                    if (ds1 == null || ds1.Tables.Count == 0)
                    {
                        dataGridView1.DataSource = null;
                        return;
                    }
                    if (ds1.Tables.Count > 0)
                        dataGridView1.DataSource = ds1.Tables[0];
                }
            }
            catch (System.Exception ex)
            {
                CDebug.SendError("查询数据出错: " + ex.ToString());
            }


        }

        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            showFieldWindow();
        }

        public void showFieldWindow()
        {
            if (ds1.Tables.Count > 0)
            {
                Program.fdbrow.ado1 = ado1;
                Program.fdbrow.sTabName = sTable;
                Program.fdbrow.ds1 = ds1;
                Program.fdbrow.i_RowIndex = i_RowIndex;
                Program.fdbrow.field_Ref();
                Program.fdbrow.Show();
            }
        }

        private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            i_RowIndex = e.RowIndex;
            showFieldWindow();
        }

    }
}